{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/tomasonjo/blogs/blob/master/Countries_of_the_world/Countries%20of%20the%20world%20analysis.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qugkv-nB4gc3"
   },
   "source": [
    "# Countries of the world\n",
    "\n",
    "- Updated to GDS 2.3 version and Neo4j v5\n",
    "- Link to original blog post: https://towardsdatascience.com/community-detection-of-the-countries-of-the-world-with-neo4j-graph-data-science-4d3a022f8399"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "8-RMR2Nv4fiD",
    "outputId": "208d660f-1069-4e80-b541-a3fd340b1d32"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting neo4j\n",
      "  Downloading neo4j-4.4.2.tar.gz (89 kB)\n",
      "\u001b[?25l\r",
      "\u001b[K     |███▋                            | 10 kB 19.4 MB/s eta 0:00:01\r",
      "\u001b[K     |███████▎                        | 20 kB 25.4 MB/s eta 0:00:01\r",
      "\u001b[K     |███████████                     | 30 kB 13.4 MB/s eta 0:00:01\r",
      "\u001b[K     |██████████████▋                 | 40 kB 10.3 MB/s eta 0:00:01\r",
      "\u001b[K     |██████████████████▎             | 51 kB 6.1 MB/s eta 0:00:01\r",
      "\u001b[K     |██████████████████████          | 61 kB 7.2 MB/s eta 0:00:01\r",
      "\u001b[K     |█████████████████████████▋      | 71 kB 7.7 MB/s eta 0:00:01\r",
      "\u001b[K     |█████████████████████████████▎  | 81 kB 7.4 MB/s eta 0:00:01\r",
      "\u001b[K     |████████████████████████████████| 89 kB 1.3 MB/s \n",
      "\u001b[?25hRequirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from neo4j) (2018.9)\n",
      "Building wheels for collected packages: neo4j\n",
      "  Building wheel for neo4j (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
      "  Created wheel for neo4j: filename=neo4j-4.4.2-py3-none-any.whl size=115365 sha256=43c3ddc1dbfa97e8aa3c34730811c26ad1d4e51d904fffe4d9c5c3b838379b45\n",
      "  Stored in directory: /root/.cache/pip/wheels/10/d6/28/95029d7f69690dbc3b93e4933197357987de34fbd44b50a0e4\n",
      "Successfully built neo4j\n",
      "Installing collected packages: neo4j\n",
      "Successfully installed neo4j-4.4.2\n"
     ]
    }
   ],
   "source": [
    "!pip install neo4j"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "drvMogZL5Ex6"
   },
   "source": [
    "I recommend you setup [a blank project on Neo4j Sandbox environment](https://sandbox.neo4j.com/?usecase=blank-sandbox), but you can also use other environment versions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "5DeXOj_o25i8"
   },
   "outputs": [],
   "source": [
    "# Define Neo4j connections\n",
    "from neo4j import GraphDatabase\n",
    "host = 'bolt://3.231.25.240:7687'\n",
    "user = 'neo4j'\n",
    "password = 'hatchets-visitor-axes'\n",
    "driver = GraphDatabase.driver(host,auth=(user, password))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "iSosOYR-25jA"
   },
   "outputs": [],
   "source": [
    "def drop_graph(name):\n",
    "    with driver.session() as session:\n",
    "        drop_graph_query = \"\"\"\n",
    "        CALL gds.graph.drop('{}');\n",
    "        \"\"\".format(name)\n",
    "        session.run(drop_graph_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "9ekQ13NM25jA"
   },
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import pandas as pd\n",
    "\n",
    "def read_query(query, params={}):\n",
    "    with driver.session() as session:\n",
    "        result = session.run(query, params)\n",
    "        return pd.DataFrame([r.values() for r in result], columns=result.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "UKpLZvAj25jB"
   },
   "source": [
    "### Graph schema\n",
    "We will be using the Countries of the world dataset made available on Kaggle by Fernando Lasso. Looking at the acknowledgments, the data originates from the CIA's World Factbook. Unfortunately, the contributor did not provide the year the dataset was compiled. My guess is the year 2013, but I might be wrong. The dataset contains various metrics such as area size, population, infant mortality, and more about 227 countries of the world."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "PyDKm5dR25jD"
   },
   "source": [
    "### Graph import\n",
    "\n",
    "For some reason, the numbers in the CSV file use a comma as a floating point instead of a dot (0,1 instead of 0.1). We need to preprocess the data to be able to cast the numbers to float in Neo4j. With the help of an APOC procedure <code>apoc.cypher.run</code>, we can preprocess and store the data in a single cypher query. <code>apoc.cypher.run</code> allows us to run independent subqueries within the main cypher query and is excellent for various use cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "z93mpqXv25jD",
    "outputId": "6ce3ebf0-aa95-4c10-d87d-690a3362c017"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import_query = \"\"\"\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"https://raw.githubusercontent.com/tomasonjo/blog-datasets/main/countries_of_the_world.csv\" as row\n",
    "// cleanup the data and replace comma floating point with a dot\n",
    "CALL apoc.cypher.run(\n",
    "    \"UNWIND keys($row) as key \n",
    "     WITH row,\n",
    "          key,\n",
    "          toFloat(replace(row[key],',','.')) as clean_value\n",
    "          // exclude string properties\n",
    "          WHERE NOT key in ['Country','Region'] \n",
    "          RETURN collect([key,clean_value]) as keys\", \n",
    "     {row:row}) YIELD value\n",
    "MERGE (c:Country{name:trim(row.Country)})\n",
    "SET c+= apoc.map.fromPairs(value.keys)\n",
    "MERGE (r:Region{name:trim(row.Region)})\n",
    "MERGE (c)-[:PART_OF]->(r)\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(import_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3J7I1k7b25jF"
   },
   "source": [
    "### Identify missing values\n",
    "Another useful APOC procedure is <code>apoc.meta.nodeTypeProperties</code>. With it, we can examine the node property schema of the graph. We will use it to identify how many missing values each feature of the country has."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 363
    },
    "id": "c4pDpXio25jF",
    "outputId": "9b17ec6d-3e34-4f98-fd6a-de7a4141e702"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>propertyName</th>\n",
       "      <th>missing_value</th>\n",
       "      <th>pct_missing_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Climate</td>\n",
       "      <td>22</td>\n",
       "      <td>0.096916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Literacy (%)</td>\n",
       "      <td>18</td>\n",
       "      <td>0.079295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Industry</td>\n",
       "      <td>16</td>\n",
       "      <td>0.070485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Agriculture</td>\n",
       "      <td>15</td>\n",
       "      <td>0.066079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Service</td>\n",
       "      <td>15</td>\n",
       "      <td>0.066079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Phones (per 1000)</td>\n",
       "      <td>4</td>\n",
       "      <td>0.017621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Deathrate</td>\n",
       "      <td>4</td>\n",
       "      <td>0.017621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Net migration</td>\n",
       "      <td>3</td>\n",
       "      <td>0.013216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Infant mortality (per 1000 births)</td>\n",
       "      <td>3</td>\n",
       "      <td>0.013216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Birthrate</td>\n",
       "      <td>3</td>\n",
       "      <td>0.013216</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         propertyName  missing_value  pct_missing_value\n",
       "0                             Climate             22           0.096916\n",
       "1                        Literacy (%)             18           0.079295\n",
       "2                            Industry             16           0.070485\n",
       "3                         Agriculture             15           0.066079\n",
       "4                             Service             15           0.066079\n",
       "5                   Phones (per 1000)              4           0.017621\n",
       "6                           Deathrate              4           0.017621\n",
       "7                       Net migration              3           0.013216\n",
       "8  Infant mortality (per 1000 births)              3           0.013216\n",
       "9                           Birthrate              3           0.013216"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "identify_missing_values_query = \"\"\"\n",
    "\n",
    "// Only look at properties of nodes labeled \"Country\"\n",
    "CALL apoc.meta.nodeTypeProperties({labels:['Country']})\n",
    "YIELD propertyName, propertyObservations, totalObservations\n",
    "RETURN propertyName,\n",
    "       (totalObservations - propertyObservations) as missing_value,\n",
    "       (totalObservations - propertyObservations) / toFloat(totalObservations) as pct_missing_value\n",
    "ORDER BY pct_missing_value DESC LIMIT 10\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(identify_missing_values_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "V858FLaB25jG"
   },
   "source": [
    "It looks like we don't have many missing values. However, we will disregard features with more than four missing values from our further analysis for the sake of simplicity.\n",
    "### High correlation filter\n",
    "High correlation filter is a simple data dimensionality reduction technique. Features with high correlation are likely to carry similar information and are more linearly dependant. Using multiple features with related information can bring down the performance of various models and can be avoided by dropping one of the two correlating features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 363
    },
    "id": "avNWFhF925jH",
    "outputId": "b7c88d8a-9f54-4884-c95a-4b73cb6c21a2"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>feature</th>\n",
       "      <th>compare_feature</th>\n",
       "      <th>correlation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Birthrate</td>\n",
       "      <td>Infant mortality (per 1000 births)</td>\n",
       "      <td>0.841210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GDP ($ per capita)</td>\n",
       "      <td>Phones (per 1000)</td>\n",
       "      <td>0.828151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Deathrate</td>\n",
       "      <td>Infant mortality (per 1000 births)</td>\n",
       "      <td>0.661350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Area (sq. mi.)</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.469985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Birthrate</td>\n",
       "      <td>Deathrate</td>\n",
       "      <td>0.420948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>GDP ($ per capita)</td>\n",
       "      <td>Net migration</td>\n",
       "      <td>0.381256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Coastline (coast/area ratio)</td>\n",
       "      <td>Crops (%)</td>\n",
       "      <td>0.338594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Phones (per 1000)</td>\n",
       "      <td>Pop. Density (per sq. mi.)</td>\n",
       "      <td>0.280954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Coastline (coast/area ratio)</td>\n",
       "      <td>Pop. Density (per sq. mi.)</td>\n",
       "      <td>0.241690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Net migration</td>\n",
       "      <td>Phones (per 1000)</td>\n",
       "      <td>0.236930</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        feature                     compare_feature  \\\n",
       "0                     Birthrate  Infant mortality (per 1000 births)   \n",
       "1            GDP ($ per capita)                   Phones (per 1000)   \n",
       "2                     Deathrate  Infant mortality (per 1000 births)   \n",
       "3                Area (sq. mi.)                          Population   \n",
       "4                     Birthrate                           Deathrate   \n",
       "5            GDP ($ per capita)                       Net migration   \n",
       "6  Coastline (coast/area ratio)                           Crops (%)   \n",
       "7             Phones (per 1000)          Pop. Density (per sq. mi.)   \n",
       "8  Coastline (coast/area ratio)          Pop. Density (per sq. mi.)   \n",
       "9                 Net migration                   Phones (per 1000)   \n",
       "\n",
       "   correlation  \n",
       "0     0.841210  \n",
       "1     0.828151  \n",
       "2     0.661350  \n",
       "3     0.469985  \n",
       "4     0.420948  \n",
       "5     0.381256  \n",
       "6     0.338594  \n",
       "7     0.280954  \n",
       "8     0.241690  \n",
       "9     0.236930  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "high_correlation_query = \"\"\"\n",
    "\n",
    "// Only look at properties of nodes labeled \"Country\"\n",
    "CALL apoc.meta.nodeTypeProperties({labels:['Country']})\n",
    "YIELD propertyName, propertyObservations, totalObservations\n",
    "WITH propertyName,\n",
    "       (totalObservations - propertyObservations) as missing_value\n",
    "// filter our features with more than 5 missing values\n",
    "WHERE missing_value < 5 AND propertyName <> 'name'\n",
    "WITH collect(propertyName) as features\n",
    "MATCH (c:Country)\n",
    "UNWIND features as feature\n",
    "UNWIND features as compare_feature\n",
    "WITH feature,\n",
    "     compare_feature,\n",
    "     collect(coalesce(c[feature],0)) as vector_1,\n",
    "     collect(coalesce(c[compare_feature],0)) as vector_2\n",
    "// avoid comparing with a feature with itself\n",
    "WHERE feature < compare_feature\n",
    "RETURN feature,\n",
    "       compare_feature,\n",
    "       gds.similarity.pearson(vector_1, vector_2) AS correlation\n",
    "ORDER BY correlation DESC LIMIT 10\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(high_correlation_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KqcIxTJP25jI"
   },
   "source": [
    "Interesting to see that birth rate and infant mortality are very correlated. The death rate is also quite correlated with infant mortality, so we will drop the birth and death rate but keep the infant mortality. The number of phones and net migration seems to be correlated with the GDP. We will drop them both as well and keep the GDP. We will also cut the population and retain both the area and population density, which carry similar information.\n",
    "### Feature statistics\n",
    "At this point, we are left with eight features. We will examine their distributions with the <code>apoc.agg.statistics</code> function. It calculates numeric statistics such as minimum, maximum, and percentile ranks for a collection of values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 300
    },
    "id": "SfothkMb25jI",
    "outputId": "62b7fd71-ae25-4aab-95a6-ef9336139876"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>potential_feature</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "      <th>stdev</th>\n",
       "      <th>p50</th>\n",
       "      <th>p75</th>\n",
       "      <th>p95</th>\n",
       "      <th>p99</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Other (%)</td>\n",
       "      <td>33.33</td>\n",
       "      <td>100.00</td>\n",
       "      <td>81.64</td>\n",
       "      <td>16.10</td>\n",
       "      <td>85.70</td>\n",
       "      <td>95.44</td>\n",
       "      <td>99.81</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Crops (%)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>50.68</td>\n",
       "      <td>4.56</td>\n",
       "      <td>8.34</td>\n",
       "      <td>1.03</td>\n",
       "      <td>4.44</td>\n",
       "      <td>20.00</td>\n",
       "      <td>45.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arable (%)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>62.11</td>\n",
       "      <td>13.80</td>\n",
       "      <td>13.01</td>\n",
       "      <td>10.42</td>\n",
       "      <td>20.00</td>\n",
       "      <td>40.54</td>\n",
       "      <td>55.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Coastline (coast/area ratio)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>870.66</td>\n",
       "      <td>21.17</td>\n",
       "      <td>72.13</td>\n",
       "      <td>0.73</td>\n",
       "      <td>10.32</td>\n",
       "      <td>92.31</td>\n",
       "      <td>310.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Infant mortality (per 1000 births)</td>\n",
       "      <td>2.29</td>\n",
       "      <td>191.19</td>\n",
       "      <td>35.51</td>\n",
       "      <td>35.31</td>\n",
       "      <td>20.97</td>\n",
       "      <td>55.51</td>\n",
       "      <td>103.32</td>\n",
       "      <td>143.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pop. Density (per sq. mi.)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>16271.50</td>\n",
       "      <td>379.05</td>\n",
       "      <td>1656.53</td>\n",
       "      <td>78.80</td>\n",
       "      <td>188.50</td>\n",
       "      <td>838.60</td>\n",
       "      <td>6482.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>GDP ($ per capita)</td>\n",
       "      <td>500.00</td>\n",
       "      <td>55100.00</td>\n",
       "      <td>9689.85</td>\n",
       "      <td>10026.91</td>\n",
       "      <td>5500.03</td>\n",
       "      <td>15700.06</td>\n",
       "      <td>29600.12</td>\n",
       "      <td>37800.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Area (sq. mi.)</td>\n",
       "      <td>2.00</td>\n",
       "      <td>17075200.00</td>\n",
       "      <td>598227.59</td>\n",
       "      <td>1786336.93</td>\n",
       "      <td>86600.50</td>\n",
       "      <td>437074.00</td>\n",
       "      <td>2345424.00</td>\n",
       "      <td>9631424.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    potential_feature     min          max       mean  \\\n",
       "0                           Other (%)   33.33       100.00      81.64   \n",
       "1                           Crops (%)    0.00        50.68       4.56   \n",
       "2                          Arable (%)    0.00        62.11      13.80   \n",
       "3        Coastline (coast/area ratio)    0.00       870.66      21.17   \n",
       "4  Infant mortality (per 1000 births)    2.29       191.19      35.51   \n",
       "5          Pop. Density (per sq. mi.)    0.00     16271.50     379.05   \n",
       "6                  GDP ($ per capita)  500.00     55100.00    9689.85   \n",
       "7                      Area (sq. mi.)    2.00  17075200.00  598227.59   \n",
       "\n",
       "        stdev       p50        p75         p95         p99  \n",
       "0       16.10     85.70      95.44       99.81      100.00  \n",
       "1        8.34      1.03       4.44       20.00       45.71  \n",
       "2       13.01     10.42      20.00       40.54       55.30  \n",
       "3       72.13      0.73      10.32       92.31      310.69  \n",
       "4       35.31     20.97      55.51      103.32      143.64  \n",
       "5     1656.53     78.80     188.50      838.60     6482.22  \n",
       "6    10026.91   5500.03   15700.06    29600.12    37800.25  \n",
       "7  1786336.93  86600.50  437074.00  2345424.00  9631424.00  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_stats_query = \"\"\"\n",
    "\n",
    "// define excluded features\n",
    "WITH ['name', \n",
    "      'Deathrate', \n",
    "      'Birthrate',\n",
    "      'Phones (per 1000)',\n",
    "      'Net migration', \n",
    "      'Population'] as excluded_features\n",
    "CALL apoc.meta.nodeTypeProperties({labels:['Country']})\n",
    "YIELD propertyName, propertyObservations, totalObservations\n",
    "WITH propertyName,\n",
    "       (totalObservations - propertyObservations) as missing_value\n",
    "WHERE missing_value < 5 AND \n",
    "      NOT propertyName in excluded_features\n",
    "// Reduce to a single row\n",
    "WITH collect(propertyName) as potential_features\n",
    "MATCH (c:Country)\n",
    "UNWIND potential_features as potential_feature\n",
    "WITH potential_feature, \n",
    "     apoc.agg.statistics(c[potential_feature],\n",
    "                        [0.5,0.75,0.9,0.95,0.99]) as stats\n",
    "RETURN potential_feature, \n",
    "       round(stats.min,2) as min, \n",
    "       round(stats.max,2) as max, \n",
    "       round(stats.mean,2) as mean, \n",
    "       round(stats.stdev,2) as stdev,\n",
    "       round(stats.`0.5`,2) as p50,\n",
    "       round(stats.`0.75`,2) as p75,\n",
    "       round(stats.`0.95`,2) as p95,\n",
    "       round(stats.`0.99`,2) as p99\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(feature_stats_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "IPbg0N1f25jJ"
   },
   "source": [
    "The Federated state of Micronesia has the ratio of coast to area at 870, which is pretty impressive. On the other hand, there are a total of 44 countries in the world with zero coastlines. Another fun fact is that Greenland has a population density rounded to 0 per square mile with its 56361 inhabitants and 2166086 square miles. It might be a cool place to perform social distancing.\n",
    "We can observe that most of the features appear to be descriptive, except for the Other (%), which is mostly between 80 and 100. Due to the low variance, we will ignore it in our further analysis.\n",
    "### Populate the missing values\n",
    "We are left with seven features that we are going to use to infer a similarity network between countries. One thing we need to do before that is to populate the missing values. We will use a simple method and fill in the missing values of the features with the average value of the region the country is part of."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "b3r7_G9925jJ",
    "outputId": "22ba09d6-ff26-40a0-c4c2-5d4b99aade6e"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>'missing values populated'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>missing values populated</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  'missing values populated'\n",
       "0   missing values populated"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "populate_missing_values = \"\"\"\n",
    "\n",
    "UNWIND [\"Arable (%)\",\n",
    "        \"Crops (%)\",\n",
    "        \"Infant mortality (per 1000 births)\",\n",
    "        \"GDP ($ per capita)\"] as feature\n",
    "MATCH (c:Country)\n",
    "WHERE c[feature] IS null\n",
    "MATCH (c)-[:PART_OF]->(r:Region)<-[:PART_OF]-(other:Country)\n",
    "WHERE other[feature] IS NOT null\n",
    "WITH c,feature,avg(other[feature]) as avg_value\n",
    "CALL apoc.create.setProperty(c, feature, avg_value) \n",
    "YIELD node\n",
    "RETURN distinct 'missing values populated'\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(populate_missing_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Q9lmKAbQ25jL"
   },
   "source": [
    "### Graph data science library\n",
    "With Neo4j's Graph Data Science library, we can run more than 50 different graph algorithms directly in Neo4j. Algorithms are exposed as cypher procedures, similar to the APOC procedures we've seen above.\n",
    "GDS uses a projection of the stored graph, that is entirely in-memory to achieve faster execution times."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "R4GNE06m25jK"
   },
   "source": [
    "### Similarity network with cosine similarity\n",
    "First, we much project the in-memory graph with GDS 2.0\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "PvPiKn2y6LrG",
    "outputId": "455d5d9f-2720-493e-bcd7-2b08ad7a803f"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nodeProjection</th>\n",
       "      <th>relationshipProjection</th>\n",
       "      <th>graphName</th>\n",
       "      <th>nodeCount</th>\n",
       "      <th>relationshipCount</th>\n",
       "      <th>projectMillis</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'Country': {'label': 'Country', 'properties':...</td>\n",
       "      <td>{'__ALL__': {'orientation': 'NATURAL', 'indexI...</td>\n",
       "      <td>countries</td>\n",
       "      <td>227</td>\n",
       "      <td>0</td>\n",
       "      <td>11592</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      nodeProjection  \\\n",
       "0  {'Country': {'label': 'Country', 'properties':...   \n",
       "\n",
       "                              relationshipProjection  graphName  nodeCount  \\\n",
       "0  {'__ALL__': {'orientation': 'NATURAL', 'indexI...  countries        227   \n",
       "\n",
       "   relationshipCount  projectMillis  \n",
       "0                  0          11592  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "project_graph_query = \"\"\"\n",
    "CALL gds.graph.project('countries', 'Country', '*', \n",
    "  {nodeProperties:['Arable (%)', 'Crops (%)', 'Infant mortality (per 1000 births)', 'GDP ($ per capita)',\n",
    "    'Coastline (coast/area ratio)', 'Pop. Density (per sq. mi.)', 'Area (sq. mi.)']})\n",
    "\"\"\"\n",
    "\n",
    "read_query(project_graph_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dPQ2byIo25jJ"
   },
   "source": [
    "### MinMax normalization\n",
    "Last but not least, we have to normalize our features to prevent any single feature dominating over others due to a larger scale. We will use the simple MinMax method of normalization to rescale features between 0 and 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "A3cTmugM6_v1",
    "outputId": "36c8ea12-2d03-4e34-c5db-eb21d5f52deb"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nodePropertiesWritten</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nodePropertiesWritten\n",
       "0                    227"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "minmax_normalization_query = \"\"\"\n",
    "CALL gds.alpha.scaleProperties.mutate('countries', {\n",
    "  nodeProperties:['Arable (%)', 'Crops (%)', 'Infant mortality (per 1000 births)', 'GDP ($ per capita)',\n",
    "    'Coastline (coast/area ratio)', 'Pop. Density (per sq. mi.)', 'Area (sq. mi.)'],\n",
    "  scaler: 'MINMAX',\n",
    "  mutateProperty: 'countryFeatures'\n",
    "}) YIELD nodePropertiesWritten\n",
    "\"\"\"\n",
    "\n",
    "read_query(minmax_normalization_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vqHtYsb06k7d"
   },
   "source": [
    "We have finished the data preprocessing and can focus on the data analysis part. The first step of the analysis is to infer a similarity network with the help of the cosine similarity algorithm. We build a vector for each country based on the selected features and compare the cosine similarity between each pair of countries. If the similarity is above the predefined threshold, we store back the results in the form of a relationship between the pair of similar nodes. Defining an optimal threshold is a mix of art and science, and you'll get better with practice. Ideally, you want to infer a sparse graph as community detection algorithms do not perform well on complete or dense graphs. In this example, we will use the similarityCutoff value of 0.8 (range between -1 and 1). Alongside the similarity threshold, we will also use the topK parameter to store only the top 10 similar neighbors. We do this to ensure a sparser graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 159
    },
    "id": "fcLCEt5R25jK",
    "outputId": "fcf2c331-a673-4370-9d56-00c953113be3"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ranIterations</th>\n",
       "      <th>nodePairsConsidered</th>\n",
       "      <th>didConverge</th>\n",
       "      <th>preProcessingMillis</th>\n",
       "      <th>computeMillis</th>\n",
       "      <th>mutateMillis</th>\n",
       "      <th>postProcessingMillis</th>\n",
       "      <th>nodesCompared</th>\n",
       "      <th>relationshipsWritten</th>\n",
       "      <th>similarityDistribution</th>\n",
       "      <th>configuration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>78164</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>507</td>\n",
       "      <td>216</td>\n",
       "      <td>-1</td>\n",
       "      <td>227</td>\n",
       "      <td>2257</td>\n",
       "      <td>{'p1': 0.8574447631835938, 'max': 0.9999618530...</td>\n",
       "      <td>{'topK': 10, 'maxIterations': 100, 'randomJoin...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ranIterations  nodePairsConsidered  didConverge  preProcessingMillis  \\\n",
       "0              5                78164         True                    0   \n",
       "\n",
       "   computeMillis  mutateMillis  postProcessingMillis  nodesCompared  \\\n",
       "0            507           216                    -1            227   \n",
       "\n",
       "   relationshipsWritten                             similarityDistribution  \\\n",
       "0                  2257  {'p1': 0.8574447631835938, 'max': 0.9999618530...   \n",
       "\n",
       "                                       configuration  \n",
       "0  {'topK': 10, 'maxIterations': 100, 'randomJoin...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cosine_similarity_query = \"\"\"\n",
    "CALL gds.knn.mutate('countries', \n",
    "  {similarityCutoff:0.8, topK:10, nodeProperties: {countryFeatures: 'COSINE'},\n",
    "   mutateRelationshipType: 'SIMILAR', mutateProperty:'score'})\n",
    "\"\"\"\n",
    "\n",
    "read_query(cosine_similarity_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "aS3qw8QN25jM"
   },
   "source": [
    "### Weakly connected components\n",
    "More often than not, we start the graph analysis with the weakly connected components algorithm. It is a community detection algorithm used to find disconnected networks or islands within our graph. As we are only interested in the count of disconnected components, we can run the stats variant of the algorithm."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "fMNY-jFF25jM",
    "outputId": "679e7ebf-bf3d-4e3f-a7c4-888fb3b5830c"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>componentCount</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "      <th>p50</th>\n",
       "      <th>p75</th>\n",
       "      <th>p90</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>227</td>\n",
       "      <td>227</td>\n",
       "      <td>227.0</td>\n",
       "      <td>227</td>\n",
       "      <td>227</td>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   componentCount  min  max   mean  p50  p75  p90\n",
       "0               1  227  227  227.0  227  227  227"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wcc_query = \"\"\"\n",
    "\n",
    "CALL gds.wcc.stats('countries', {relationshipTypes:['SIMILAR']})\n",
    "YIELD componentCount, componentDistribution\n",
    "RETURN componentCount, \n",
    "       componentDistribution.min as min,\n",
    "       componentDistribution.max as max,\n",
    "       componentDistribution.mean as mean,\n",
    "       componentDistribution.p50 as p50,\n",
    "       componentDistribution.p75 as p75,\n",
    "       componentDistribution.p90 as p90\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(wcc_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DVAmOn4D25jM"
   },
   "source": [
    "The algorithm found only a single component within our graph. This is a favorable outcome as disconnected islands can skew the results of various other graph algorithms.\n",
    "### Louvain algorithm\n",
    "Another community detection algorithm is the Louvain algorithm. In basic terms, densely connected nodes are more likely to form a community. It relies on the modularity optimization to extract communities. The modularity optimization is performed in two steps. The first step involves optimizing the modularity locally. In the second step, it aggregates nodes belonging to the same community into a single node and builds a new network from those aggregated nodes. These two steps are repeated iteratively until a maximum of modularity is attained. A subtle side effect of these iterations is that we can take a look at the community structure at the end of each iteration, hence the Louvain algorithm is regarded as a hierarchical community detection algorithm. To include hierarchical community results, we must set the <code>includeIntermediateCommunities</code> parameter value to true."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "8tRYco1v25jN",
    "outputId": "c7c471a4-aea0-4634-bef9-6a09f16d9d59"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ranLevels</th>\n",
       "      <th>communityCount</th>\n",
       "      <th>modularity</th>\n",
       "      <th>modularities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0.734601</td>\n",
       "      <td>[0.6954974323961155, 0.734601100224988]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ranLevels  communityCount  modularity  \\\n",
       "0          2               8    0.734601   \n",
       "\n",
       "                              modularities  \n",
       "0  [0.6954974323961155, 0.734601100224988]  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "louvain_algo_query = \"\"\"\n",
    "\n",
    "CALL gds.louvain.write('countries',  \n",
    "    {maxIterations:20,\n",
    "     relationshipTypes:['SIMILAR'],\n",
    "     includeIntermediateCommunities:true,\n",
    "     writeProperty:'louvain'})\n",
    "YIELD ranLevels, communityCount,modularity,modularities\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(louvain_algo_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dAVvwSvr25jN"
   },
   "source": [
    "We can observe by the <code>ranLevels</code> value that the Louvain algorithm found two levels of communities in our network. On the final level, it found eight g. We can now examine the extracted communities of the last level and compare their feature averages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 300
    },
    "id": "Mb9vtbLa25jN",
    "outputId": "7099c934-8b44-4c1c-f0eb-26ea6f919207"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>community</th>\n",
       "      <th>community_size</th>\n",
       "      <th>pct_arable</th>\n",
       "      <th>pct_crops</th>\n",
       "      <th>infant_mortality</th>\n",
       "      <th>gdp</th>\n",
       "      <th>coastline</th>\n",
       "      <th>population_density</th>\n",
       "      <th>area_size</th>\n",
       "      <th>example_members</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>46</td>\n",
       "      <td>5.520652</td>\n",
       "      <td>1.417609</td>\n",
       "      <td>8.672609</td>\n",
       "      <td>22271.739130</td>\n",
       "      <td>41.446304</td>\n",
       "      <td>1235.871739</td>\n",
       "      <td>3.337008e+05</td>\n",
       "      <td>[Andorra, Anguilla, Aruba]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>54</td>\n",
       "      <td>23</td>\n",
       "      <td>19.527101</td>\n",
       "      <td>3.297601</td>\n",
       "      <td>8.276522</td>\n",
       "      <td>19091.304348</td>\n",
       "      <td>11.173478</td>\n",
       "      <td>239.895652</td>\n",
       "      <td>3.163263e+05</td>\n",
       "      <td>[Argentina, Belgium, British Virgin Is.]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>43</td>\n",
       "      <td>23</td>\n",
       "      <td>4.273043</td>\n",
       "      <td>0.563913</td>\n",
       "      <td>29.380261</td>\n",
       "      <td>8393.913043</td>\n",
       "      <td>0.330000</td>\n",
       "      <td>23.886957</td>\n",
       "      <td>3.101448e+06</td>\n",
       "      <td>[Algeria, Australia, Belize]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50</td>\n",
       "      <td>41</td>\n",
       "      <td>31.129268</td>\n",
       "      <td>3.028049</td>\n",
       "      <td>20.186341</td>\n",
       "      <td>7509.756098</td>\n",
       "      <td>15.717561</td>\n",
       "      <td>194.495122</td>\n",
       "      <td>2.044609e+05</td>\n",
       "      <td>[Albania, Antigua &amp; Barbuda, Armenia]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>57</td>\n",
       "      <td>26</td>\n",
       "      <td>13.234231</td>\n",
       "      <td>22.198077</td>\n",
       "      <td>23.057591</td>\n",
       "      <td>4465.384615</td>\n",
       "      <td>66.923462</td>\n",
       "      <td>370.726923</td>\n",
       "      <td>3.770538e+04</td>\n",
       "      <td>[American Samoa, Cook Islands, Dominica]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>30</td>\n",
       "      <td>10</td>\n",
       "      <td>22.116000</td>\n",
       "      <td>10.184000</td>\n",
       "      <td>55.267000</td>\n",
       "      <td>2100.000000</td>\n",
       "      <td>2.702000</td>\n",
       "      <td>180.400000</td>\n",
       "      <td>2.983568e+05</td>\n",
       "      <td>[Burundi, Comoros, Ecuador]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>14.853575</td>\n",
       "      <td>1.090783</td>\n",
       "      <td>69.105833</td>\n",
       "      <td>1870.833333</td>\n",
       "      <td>3.340000</td>\n",
       "      <td>100.829167</td>\n",
       "      <td>3.194362e+05</td>\n",
       "      <td>[Azerbaijan, Benin, Burkina Faso]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>23</td>\n",
       "      <td>34</td>\n",
       "      <td>3.931765</td>\n",
       "      <td>1.443529</td>\n",
       "      <td>91.808529</td>\n",
       "      <td>1435.294118</td>\n",
       "      <td>4.170882</td>\n",
       "      <td>37.926471</td>\n",
       "      <td>6.419174e+05</td>\n",
       "      <td>[Afghanistan, Angola, Bhutan]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community  community_size  pct_arable  pct_crops  infant_mortality  \\\n",
       "0         12              46    5.520652   1.417609          8.672609   \n",
       "1         54              23   19.527101   3.297601          8.276522   \n",
       "2         43              23    4.273043   0.563913         29.380261   \n",
       "3         50              41   31.129268   3.028049         20.186341   \n",
       "4         57              26   13.234231  22.198077         23.057591   \n",
       "5         30              10   22.116000  10.184000         55.267000   \n",
       "6         21              24   14.853575   1.090783         69.105833   \n",
       "7         23              34    3.931765   1.443529         91.808529   \n",
       "\n",
       "            gdp  coastline  population_density     area_size  \\\n",
       "0  22271.739130  41.446304         1235.871739  3.337008e+05   \n",
       "1  19091.304348  11.173478          239.895652  3.163263e+05   \n",
       "2   8393.913043   0.330000           23.886957  3.101448e+06   \n",
       "3   7509.756098  15.717561          194.495122  2.044609e+05   \n",
       "4   4465.384615  66.923462          370.726923  3.770538e+04   \n",
       "5   2100.000000   2.702000          180.400000  2.983568e+05   \n",
       "6   1870.833333   3.340000          100.829167  3.194362e+05   \n",
       "7   1435.294118   4.170882           37.926471  6.419174e+05   \n",
       "\n",
       "                            example_members  \n",
       "0                [Andorra, Anguilla, Aruba]  \n",
       "1  [Argentina, Belgium, British Virgin Is.]  \n",
       "2              [Algeria, Australia, Belize]  \n",
       "3     [Albania, Antigua & Barbuda, Armenia]  \n",
       "4  [American Samoa, Cook Islands, Dominica]  \n",
       "5               [Burundi, Comoros, Ecuador]  \n",
       "6         [Azerbaijan, Benin, Burkina Faso]  \n",
       "7             [Afghanistan, Angola, Bhutan]  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_level_communities =\"\"\"\n",
    "\n",
    "MATCH (c:Country)\n",
    "RETURN c.louvain[-1] as community,\n",
    "       count(*) as community_size,\n",
    "       avg(c['Arable (%)']) as pct_arable,\n",
    "       avg(c['Crops (%)']) as pct_crops, \n",
    "       avg(c['Infant mortality (per 1000 births)']) as infant_mortality,\n",
    "       avg(c['GDP ($ per capita)']) as gdp,\n",
    "       avg(c['Coastline (coast/area ratio)']) as coastline,\n",
    "       avg(c['Pop. Density (per sq. mi.)']) as population_density,\n",
    "       avg(c['Area (sq. mi.)']) as area_size,\n",
    "       collect(c['name'])[..3] as example_members\n",
    "ORDER BY gdp DESC\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(final_level_communities)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ioqDNQ2r25jN"
   },
   "source": [
    "Louvain algorithm found eight distinct communities within the similarity network. The biggest group has 51 countries as members and has the largest average GDP at almost 22 thousand dollars. They are second in infant mortality and the coastline ratio but lead in population density by a large margin. There are two communities with an average GDP of around 20 thousand dollars, and then we can observe a steep drop to 7000 dollars in third place. With the decline in GDP, we can also find the rise of infant mortality almost linearly. Another fascinating insight is that most of the more impoverished communities have little to no coastline.\n",
    "### Find representatives of communities with PageRank\n",
    "We can assess the top representatives of the final level communities with the PageRank algorithm. If we assume that each SIMILAR relationship is a vote of similarity between countries, the PageRank algorithm will assign the highest score to the most similar countries within the community. We will execute the PageRank algorithm for each community separately and consider only nodes and relationships within the given community. This can be easily achieved with cypher projection without any additional transformations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 300
    },
    "id": "s3jx3aLJ25jN",
    "outputId": "157be139-2c30-4952-f92d-c018cb42617f"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>community</th>\n",
       "      <th>top_5_representatives</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23</td>\n",
       "      <td>[Afghanistan, Angola, Bhutan, Bolivia, Central...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>[Albania, Antigua &amp; Barbuda, Armenia, Banglade...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>43</td>\n",
       "      <td>[Algeria, Australia, Belize, Botswana, Brazil]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>57</td>\n",
       "      <td>[American Samoa, Cook Islands, Dominica, Domin...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>[Andorra, Anguilla, Aruba, Austria, Bahamas, The]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>54</td>\n",
       "      <td>[Argentina, Belgium, British Virgin Is., Eston...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>21</td>\n",
       "      <td>[Azerbaijan, Benin, Burkina Faso, Burma, Cambo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>30</td>\n",
       "      <td>[Burundi, Comoros, Ecuador, Ghana, Guatemala]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community                              top_5_representatives\n",
       "0         23  [Afghanistan, Angola, Bhutan, Bolivia, Central...\n",
       "1         50  [Albania, Antigua & Barbuda, Armenia, Banglade...\n",
       "2         43     [Algeria, Australia, Belize, Botswana, Brazil]\n",
       "3         57  [American Samoa, Cook Islands, Dominica, Domin...\n",
       "4         12  [Andorra, Anguilla, Aruba, Austria, Bahamas, The]\n",
       "5         54  [Argentina, Belgium, British Virgin Is., Eston...\n",
       "6         21  [Azerbaijan, Benin, Burkina Faso, Burma, Cambo...\n",
       "7         30      [Burundi, Comoros, Ecuador, Ghana, Guatemala]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_representatives_query = \"\"\"\n",
    "\n",
    "WITH 'MATCH (c:Country) WHERE c.louvain[-1] = $community \n",
    "      RETURN id(c) as id' as nodeQuery,\n",
    "     'MATCH (s:Country)-[:SIMILAR]-(t:Country) \n",
    "      RETURN id(s) as source, id(t) as target' as relQuery\n",
    "MATCH (c:Country)\n",
    "WITH distinct c.louvain[-1] as community, nodeQuery, relQuery\n",
    "CALL gds.graph.project.cypher(toString(community), nodeQuery, relQuery, {parameters:{community:community}})\n",
    "YIELD nodeCount\n",
    "CALL gds.pageRank.stream(toString(community))\n",
    "YIELD nodeId, score\n",
    "WITH community, nodeId,score\n",
    "ORDER BY score DESC\n",
    "RETURN community, \n",
    "       collect(gds.util.asNode(nodeId).name)[..5] as top_5_representatives\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(top_representatives_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8ybMwi_b25jO"
   },
   "source": [
    "### Hierarchical communities based on the Louvain algorithm\n",
    "We mentioned before that the Louvain algorithm can be used to find hierarchical communities with the includeIntermediateCommunities parameter and that in our example, it found two levels of communities. We will now examine the groups of countries on the first level. A rule of thumb is that communities on a lower level will be more granular and smaller."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 520
    },
    "id": "qJkNuPyq25jO",
    "outputId": "6428afda-471f-4741-a4f7-5ce8a56e1da4"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>community</th>\n",
       "      <th>community_size</th>\n",
       "      <th>pct_arable</th>\n",
       "      <th>pct_crops</th>\n",
       "      <th>infant_mortality</th>\n",
       "      <th>gdp</th>\n",
       "      <th>coastline</th>\n",
       "      <th>population_density</th>\n",
       "      <th>area_size</th>\n",
       "      <th>example_members</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "      <td>10.829000</td>\n",
       "      <td>2.222500</td>\n",
       "      <td>6.239500</td>\n",
       "      <td>25830.000000</td>\n",
       "      <td>15.623500</td>\n",
       "      <td>203.535000</td>\n",
       "      <td>6.146537e+05</td>\n",
       "      <td>[Aruba, Austria, Bermuda]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>54</td>\n",
       "      <td>14</td>\n",
       "      <td>20.745952</td>\n",
       "      <td>1.208201</td>\n",
       "      <td>6.735714</td>\n",
       "      <td>21335.714286</td>\n",
       "      <td>9.730714</td>\n",
       "      <td>296.542857</td>\n",
       "      <td>2.993842e+05</td>\n",
       "      <td>[Argentina, Belgium, Estonia]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38</td>\n",
       "      <td>26</td>\n",
       "      <td>1.437308</td>\n",
       "      <td>0.798462</td>\n",
       "      <td>10.544231</td>\n",
       "      <td>19534.615385</td>\n",
       "      <td>61.310000</td>\n",
       "      <td>2029.976923</td>\n",
       "      <td>1.175833e+05</td>\n",
       "      <td>[Andorra, Anguilla, Bahamas, The]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>17.631111</td>\n",
       "      <td>6.547778</td>\n",
       "      <td>10.673333</td>\n",
       "      <td>15600.000000</td>\n",
       "      <td>13.417778</td>\n",
       "      <td>151.777778</td>\n",
       "      <td>3.426807e+05</td>\n",
       "      <td>[British Virgin Is., Greece, Guadeloupe]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50</td>\n",
       "      <td>12</td>\n",
       "      <td>26.865000</td>\n",
       "      <td>2.245000</td>\n",
       "      <td>8.755000</td>\n",
       "      <td>13275.000000</td>\n",
       "      <td>47.305000</td>\n",
       "      <td>268.258333</td>\n",
       "      <td>2.471858e+04</td>\n",
       "      <td>[Antigua &amp; Barbuda, Barbados, Croatia]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>43</td>\n",
       "      <td>16</td>\n",
       "      <td>5.606250</td>\n",
       "      <td>0.648750</td>\n",
       "      <td>25.319375</td>\n",
       "      <td>9562.500000</td>\n",
       "      <td>0.282500</td>\n",
       "      <td>32.443750</td>\n",
       "      <td>4.310791e+06</td>\n",
       "      <td>[Algeria, Australia, Brazil]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>30</td>\n",
       "      <td>12</td>\n",
       "      <td>38.811667</td>\n",
       "      <td>2.170000</td>\n",
       "      <td>13.029167</td>\n",
       "      <td>7425.000000</td>\n",
       "      <td>4.370000</td>\n",
       "      <td>130.316667</td>\n",
       "      <td>1.547768e+05</td>\n",
       "      <td>[Belarus, Bulgaria, Cuba]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>39</td>\n",
       "      <td>7</td>\n",
       "      <td>1.225714</td>\n",
       "      <td>0.370000</td>\n",
       "      <td>38.662286</td>\n",
       "      <td>5722.857143</td>\n",
       "      <td>0.438571</td>\n",
       "      <td>4.328571</td>\n",
       "      <td>3.372373e+05</td>\n",
       "      <td>[Belize, Botswana, Gabon]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>57</td>\n",
       "      <td>15</td>\n",
       "      <td>17.153333</td>\n",
       "      <td>14.770000</td>\n",
       "      <td>22.656912</td>\n",
       "      <td>4800.000000</td>\n",
       "      <td>27.435333</td>\n",
       "      <td>537.733333</td>\n",
       "      <td>4.220207e+04</td>\n",
       "      <td>[American Samoa, Cook Islands, Dominican Repub...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>56</td>\n",
       "      <td>11</td>\n",
       "      <td>7.890000</td>\n",
       "      <td>32.327273</td>\n",
       "      <td>23.603971</td>\n",
       "      <td>4009.090909</td>\n",
       "      <td>120.770909</td>\n",
       "      <td>142.990909</td>\n",
       "      <td>3.157355e+04</td>\n",
       "      <td>[Dominica, Grenada, Kiribati]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>28.716471</td>\n",
       "      <td>4.186471</td>\n",
       "      <td>33.307647</td>\n",
       "      <td>3500.000000</td>\n",
       "      <td>1.430588</td>\n",
       "      <td>187.729412</td>\n",
       "      <td>3.664089e+05</td>\n",
       "      <td>[Albania, Armenia, Bangladesh]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>44</td>\n",
       "      <td>10</td>\n",
       "      <td>22.116000</td>\n",
       "      <td>10.184000</td>\n",
       "      <td>55.267000</td>\n",
       "      <td>2100.000000</td>\n",
       "      <td>2.702000</td>\n",
       "      <td>180.400000</td>\n",
       "      <td>2.983568e+05</td>\n",
       "      <td>[Burundi, Comoros, Ecuador]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>21</td>\n",
       "      <td>16</td>\n",
       "      <td>17.386613</td>\n",
       "      <td>1.349300</td>\n",
       "      <td>66.530000</td>\n",
       "      <td>1881.250000</td>\n",
       "      <td>4.710625</td>\n",
       "      <td>121.756250</td>\n",
       "      <td>2.988618e+05</td>\n",
       "      <td>[Azerbaijan, Benin, Burma]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>9.787500</td>\n",
       "      <td>0.573750</td>\n",
       "      <td>74.257500</td>\n",
       "      <td>1850.000000</td>\n",
       "      <td>0.598750</td>\n",
       "      <td>58.975000</td>\n",
       "      <td>3.605852e+05</td>\n",
       "      <td>[Burkina Faso, East Timor, Ethiopia]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>23</td>\n",
       "      <td>34</td>\n",
       "      <td>3.931765</td>\n",
       "      <td>1.443529</td>\n",
       "      <td>91.808529</td>\n",
       "      <td>1435.294118</td>\n",
       "      <td>4.170882</td>\n",
       "      <td>37.926471</td>\n",
       "      <td>6.419174e+05</td>\n",
       "      <td>[Afghanistan, Angola, Bhutan]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    community  community_size  pct_arable  pct_crops  infant_mortality  \\\n",
       "0          12              20   10.829000   2.222500          6.239500   \n",
       "1          54              14   20.745952   1.208201          6.735714   \n",
       "2          38              26    1.437308   0.798462         10.544231   \n",
       "3           7               9   17.631111   6.547778         10.673333   \n",
       "4          50              12   26.865000   2.245000          8.755000   \n",
       "5          43              16    5.606250   0.648750         25.319375   \n",
       "6          30              12   38.811667   2.170000         13.029167   \n",
       "7          39               7    1.225714   0.370000         38.662286   \n",
       "8          57              15   17.153333  14.770000         22.656912   \n",
       "9          56              11    7.890000  32.327273         23.603971   \n",
       "10          9              17   28.716471   4.186471         33.307647   \n",
       "11         44              10   22.116000  10.184000         55.267000   \n",
       "12         21              16   17.386613   1.349300         66.530000   \n",
       "13          0               8    9.787500   0.573750         74.257500   \n",
       "14         23              34    3.931765   1.443529         91.808529   \n",
       "\n",
       "             gdp   coastline  population_density     area_size  \\\n",
       "0   25830.000000   15.623500          203.535000  6.146537e+05   \n",
       "1   21335.714286    9.730714          296.542857  2.993842e+05   \n",
       "2   19534.615385   61.310000         2029.976923  1.175833e+05   \n",
       "3   15600.000000   13.417778          151.777778  3.426807e+05   \n",
       "4   13275.000000   47.305000          268.258333  2.471858e+04   \n",
       "5    9562.500000    0.282500           32.443750  4.310791e+06   \n",
       "6    7425.000000    4.370000          130.316667  1.547768e+05   \n",
       "7    5722.857143    0.438571            4.328571  3.372373e+05   \n",
       "8    4800.000000   27.435333          537.733333  4.220207e+04   \n",
       "9    4009.090909  120.770909          142.990909  3.157355e+04   \n",
       "10   3500.000000    1.430588          187.729412  3.664089e+05   \n",
       "11   2100.000000    2.702000          180.400000  2.983568e+05   \n",
       "12   1881.250000    4.710625          121.756250  2.988618e+05   \n",
       "13   1850.000000    0.598750           58.975000  3.605852e+05   \n",
       "14   1435.294118    4.170882           37.926471  6.419174e+05   \n",
       "\n",
       "                                      example_members  \n",
       "0                           [Aruba, Austria, Bermuda]  \n",
       "1                       [Argentina, Belgium, Estonia]  \n",
       "2                   [Andorra, Anguilla, Bahamas, The]  \n",
       "3            [British Virgin Is., Greece, Guadeloupe]  \n",
       "4              [Antigua & Barbuda, Barbados, Croatia]  \n",
       "5                        [Algeria, Australia, Brazil]  \n",
       "6                           [Belarus, Bulgaria, Cuba]  \n",
       "7                           [Belize, Botswana, Gabon]  \n",
       "8   [American Samoa, Cook Islands, Dominican Repub...  \n",
       "9                       [Dominica, Grenada, Kiribati]  \n",
       "10                     [Albania, Armenia, Bangladesh]  \n",
       "11                        [Burundi, Comoros, Ecuador]  \n",
       "12                         [Azerbaijan, Benin, Burma]  \n",
       "13               [Burkina Faso, East Timor, Ethiopia]  \n",
       "14                      [Afghanistan, Angola, Bhutan]  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_level_communities = \"\"\"\n",
    "\n",
    "MATCH (c:Country)\n",
    "RETURN c.louvain[0] as community,\n",
    "       count(*) as community_size,\n",
    "       avg(c['Arable (%)']) as pct_arable,\n",
    "       avg(c['Crops (%)']) as pct_crops, \n",
    "       avg(c['Infant mortality (per 1000 births)']) as infant_mortality,\n",
    "       avg(c['GDP ($ per capita)']) as gdp,\n",
    "       avg(c['Coastline (coast/area ratio)']) as coastline,\n",
    "       avg(c['Pop. Density (per sq. mi.)']) as population_density,\n",
    "       avg(c['Area (sq. mi.)']) as area_size,\n",
    "       collect(c['name'])[..3] as example_members\n",
    "ORDER BY gdp DESC\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "read_query(first_level_communities)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "S20aUzOJ25jO"
   },
   "source": [
    "As expected, there are almost twice as many communities on the first level compared to the second and final level. An exciting community formed in second place by the average GDP. It contains only five countries, which are quite tiny as their average area size is only 364 square miles. On the other hand, they have a very high population density of around 10000 people per square mile. Example members are Macau, Monaco, and Hong Kong."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 300
    },
    "id": "aI450Ksi25jO",
    "outputId": "94299495-50cf-47a4-9ccb-885c3c687061"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>dropped graph: 57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>dropped graph: 23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>dropped graph: 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dropped graph: 43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>dropped graph: 54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>dropped graph: 21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>dropped graph: 30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>dropped graph: 50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>dropped graph: countries</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     result\n",
       "0         dropped graph: 57\n",
       "1         dropped graph: 23\n",
       "2         dropped graph: 12\n",
       "3         dropped graph: 43\n",
       "4         dropped graph: 54\n",
       "5         dropped graph: 21\n",
       "6         dropped graph: 30\n",
       "7         dropped graph: 50\n",
       "8  dropped graph: countries"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drop_all_graphs = \"\"\"\n",
    "CALL gds.graph.list() YIELD graphName\n",
    "CALL gds.graph.drop(graphName) YIELD graphName as t\n",
    "RETURN 'dropped graph: ' + graphName AS result\n",
    "\"\"\"\n",
    "\n",
    "read_query(drop_all_graphs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "rNRoILif25jP"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "name": "Countries of the world analysis.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
